<?php
/**
 * Created by PhpStorm.
 * User: yuanjie
 * Date: 15/9/28
 * Time: 下午7:39
 */
class UserCarInfo extends Eloquent
{
    protected $table = "user_car_info";
    public static function userAuthCar($car_no){
        return DB::table('user_authentication_car')
            ->where('car_no',$car_no)
            ->where('status',1)
            ->select('vin_code','mileage','car_brand_id','car_series_id','car_model_id')
            ->first();
    }

    public static function  getCarStandardInfo($car_model_id){
        return  DB::table('anhui_data.car_standard_info')
            ->join('anhui_data.car_brand', 'anhui_data.car_standard_info.brand_name_id', '=', 'car_brand.id')
//            ->where('car_brand.status',1)
            ->where('car_standard_info.id',$car_model_id)
            ->select('car_standard_info.series_name', 'car_standard_info.model_year',
                'car_standard_info.model_name', 'car_brand.icon','car_standard_info.model_level',
                'car_standard_info.model_gas_rate','car_standard_info.model_gear_box','car_standard_info.id as car_model_id',
                'car_standard_info.series_name_id','car_standard_info.brand_name_id','car_standard_info.brand_name','car_standard_info.series_shop_id')
            ->first();

    }


    public static function  getCarStandardInfoWechat3($car_model_id){
        return  DB::table('anhui_data.car_standard_info')
            ->join('anhui_data.car_brand', 'anhui_data.car_standard_info.brand_name_id', '=', 'car_brand.id')
//            ->where('car_brand.status',1)
            ->where('anhui_data.car_standard_info.id',$car_model_id)
            ->select('anhui_data.car_standard_info.series_name', 'anhui_data.car_standard_info.model_year',
                'anhui_data.car_standard_info.model_name', 'anhui_data.car_brand.icon','anhui_data.car_standard_info.model_level',
                'anhui_data.car_standard_info.model_gas_rate','anhui_data.car_standard_info.model_gear_box','anhui_data.car_standard_info.id as car_model_id',
                'anhui_data.car_standard_info.series_name_id as car_series_id','anhui_data.car_standard_info.brand_name_id as car_brand_id','anhui_data.car_standard_info.brand_name as name','anhui_data.car_standard_info.series_shop_id')
            ->first();

    }

    /*
     * 按照 默认的 已认证的 时间倒叙 排序规则 获取用户当前可用所有车辆信息
     * dongxia 20151204
     * */

    public static function getUserCarInfoSort($userid){
        return DB::table('user_car_info')
            ->where('user_id',$userid)
            ->where('status',1)
            ->select('car_no','car_model_id','vin_code','id as user_car_info_id','is_lock','is_default')
            ->orderBy('is_default', 'desc')
            ->orderBy('is_lock', 'desc')
            ->orderBy('created_at', 'desc')
            ->get();
    }

    //查询用户有没有默认车辆
    public static $car_status = array(1,3);
    public static function detail_car($user_id){
        $default_car = UserCarInfo::join("anhui_data.car_brand","user_car_info.car_brand_id","=","anhui_data.car_brand.id")->where("is_default",1)->where("user_id",$user_id)->whereIn("user_car_info.status",UserCarInfo::$car_status)->select("is_default","user_car_info.user_id","user_car_info.created_at","user_car_info.updated_at","user_car_info.car_no","user_car_info.id","anhui_data.car_brand.icon")->first();
        return $default_car;
    }

    public static function select_word(){
        $res = 'orders.user_id,'
            .'MAX(orders.created_at) as created_at,'
            .'MAX(orders.updated_at) as updated_at,'
            .'orders.car_no,'
            .'MAX(orders.order_id) as order_id,'
            .'anhui_data.car_brand.icon,'
            .'orders.user_car_id,'
            .'MAX(orders.status) as order_status';
        return $res;
    }

    //查询用户是否有正在服务中的车辆
    public static function service_car($user_id){

        //$service_car = Orders::join("anhui_data.car_brand","orders.car_brand_id","=","anhui_data.car_brand.id")->join("user_car_info","orders.user_car_id","=","user_car_info.id")->where("orders.user_id",$user_id)->whereIn("orders.status",Orders::$car_service_status)->where("order_type",1)->whereIn("user_car_info.status",UserCarInfo::$car_status)
            //->select(DB::raw(UserCarInfo::select_word()))
            //->orderBy('created_at','desc')
            //->groupBy("orders.car_no")->get();

        $sql = "SELECT
	orders.order_id,
	orders.user_car_id,
	orders.updated_at,
	orders.status,
	orders.car_no,
	anhui_data.car_brand. icon
FROM
	orders
RIGHT JOIN (
	SELECT
		MAX(updated_at) AS max_updated_at
	FROM
		orders
	WHERE
		STATUS IN (1215, 1216, 1416, 1420)
	and
		order_type = 1
	AND user_id = $user_id
	GROUP BY
		user_car_id
) AS orders_s ON orders.updated_at = orders_s.max_updated_at
INNER JOIN user_car_info ON orders.user_car_id = user_car_info.id
INNER JOIN anhui_data.car_brand ON orders.car_brand_id = anhui_data.car_brand.id
WHERE
	user_car_info.status In (1,3)";
        $service_car = DB::select($sql);
        return $service_car;
    }

    //查询用户是否有在店车辆(大于三小时)
    public static function in_store($user_id){
        $end_date = date("Y-m-d H:i:s",time()-3*3600);
        //$in_store = Orders::join("anhui_data.car_brand","orders.car_brand_id","=","anhui_data.car_brand.id")->join("user_car_info","orders.user_car_id","=","user_car_info.id")->where("orders.user_id",$user_id)->whereIn("orders.status",Orders::$car_in_store_status)->where("orders.created_at","<=",$end_date)->where("order_type",1)->whereIn("user_car_info.status",UserCarInfo::$car_status)->select(DB::raw(UserCarInfo::select_word()))->orderBy('created_at','desc')->groupBy("orders.car_no")->get();
        $sql = "SELECT
	orders.order_id,
	orders.user_car_id,
	orders.updated_at,
	orders.status,
	orders.car_no,
	anhui_data.car_brand. icon
FROM
	orders
RIGHT JOIN (
	SELECT
		MAX(updated_at) AS max_updated_at
	FROM
		orders
	WHERE
		STATUS IN (1117, 1217, 1317, 1417, 1018)
	and
		order_type = 1
	and
	    (updated_at <= '".$end_date."')
	AND user_id = $user_id
	GROUP BY
		user_car_id
) AS orders_s ON orders.updated_at = orders_s.max_updated_at
INNER JOIN user_car_info ON orders.user_car_id = user_car_info.id
INNER JOIN anhui_data.car_brand ON orders.car_brand_id = anhui_data.car_brand.id
WHERE
	user_car_info.status In (1,3)";
        $in_store = DB::select($sql);
        return $in_store;
    }

    //查询用户三个小时内的在店车辆
    public static function three_in_store($user_id){
        $begin_date = date("Y-m-d H:i:s",time());
        $end_date = date("Y-m-d H:i:s",time()-3*3600);
        //$three_in_store =  Orders::join("anhui_data.car_brand","orders.car_brand_id","=","anhui_data.car_brand.id")->join("user_car_info","orders.user_car_id","=","user_car_info.id")->where("orders.user_id",$user_id)->whereIn("orders.status",Orders::$car_in_store_status)->whereBetween("orders.created_at",array($end_date,$begin_date))->where("order_type",1)->whereIn("user_car_info.status",UserCarInfo::$car_status)->select(DB::raw(UserCarInfo::select_word()))->orderBy('created_at','desc')->groupBy("orders.car_no")->get();
        $sql = "SELECT
	orders.order_id,
	orders.user_car_id,
	orders.updated_at,
	orders.status,
	orders.car_no,
	anhui_data.car_brand. icon
FROM
	orders
RIGHT JOIN (
	SELECT
		MAX(updated_at) AS max_updated_at
	FROM
		orders
	WHERE
		STATUS IN (1117, 1217, 1317, 1417, 1018)
	and
		order_type = 1
	and
	    (updated_at >= '".$end_date."' && updated_at <= '".$begin_date."')
	AND user_id = $user_id
	GROUP BY
		user_car_id
) AS orders_s ON orders.updated_at = orders_s.max_updated_at
INNER JOIN user_car_info ON orders.user_car_id = user_car_info.id
INNER JOIN anhui_data.car_brand ON orders.car_brand_id = anhui_data.car_brand.id
WHERE
	user_car_info.status In (1,3)";
        $three_in_store = DB::select($sql);
        return $three_in_store;
    }

    //查询用户曾到店车辆
    public static function Once_to_the_store($user_id){
        $Once_to_the_store =  UserCarInfo::where('user_id',$user_id)->whereIn('status',UserCarInfo::$car_status)->where('is_lock',1)->orderBy('created_at','desc')->get();
        return $Once_to_the_store;
    }

    /*
     * 按照 默认的 已认证的 id正序 排序规则 获取用户当前可用所有车辆信息
     * sqq 2018/08/06
     * */
    public static function getUserCarInfo($userid){
       $data = UserCarInfo::join("anhui_data.car_brand","user_car_info.car_brand_id","=","anhui_data.car_brand.id")
            ->join('anhui_data.car_standard_info','user_car_info.car_model_id','=','anhui_data.car_standard_info.id')
            ->where('user_car_info.user_id',$userid)
            ->where('user_car_info.status',1)
            ->select('car_no','car_model_id','vin_code','user_car_info.id as user_car_info_id','is_lock','is_default','anhui_data.car_brand.id as car_brand_id','anhui_data.car_standard_info.series_name', 'anhui_data.car_standard_info.model_year',
                'anhui_data.car_standard_info.model_name', 'anhui_data.car_brand.icon','anhui_data.car_standard_info.model_level',
                'anhui_data.car_standard_info.model_gas_rate','anhui_data.car_standard_info.model_gear_box','anhui_data.car_standard_info.id as car_model_id',
                'anhui_data.car_standard_info.series_name_id as car_series_id','anhui_data.car_brand.name','anhui_data.car_standard_info.series_shop_id')
            ->orderBy('user_car_info.is_default', 'desc')
            ->orderBy('user_car_info.is_lock', 'desc')
            ->orderBy('user_car_info.id', 'asc')
            ->get();
        if(!is_null($data)){
            foreach($data as $_key => $_val){
                $data[$_key]['car_brand_img_url'] = Config::get('config.car_brand_img_url');
            }
        }
        return $data;
    }

    //查询用户所有车辆
    public static function user_car($user_id){
        $user_car = UserCarInfo::join("anhui_data.car_brand","user_car_info.car_brand_id","=","anhui_data.car_brand.id")->where("user_id",$user_id)->whereIn("user_car_info.status",UserCarInfo::$car_status)->select("is_default","user_car_info.user_id","user_car_info.created_at","user_car_info.updated_at","user_car_info.car_no","user_car_info.id","anhui_data.car_brand.icon")->get();
        return $user_car;
    }

    public function user_car_info_inquiry(){
        return $this->hasOne('UserCarInfoInquiry','user_car_info_id');
    }


}